package cn.jhc.listener;

import jakarta.servlet.ServletContextEvent;
import jakarta.servlet.ServletContextListener;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTableListener implements ServletContextListener {
    private static final String CREATE_TABLE_SQL = "create table if not exists survey(" +
            "id int primary key auto_increment," +
            "name varchar(50) unique," +
            "age int check(age>0)," +
            "gender varchar(10)," +
            "occupation varchar(50)," +
            "hobbies varchar(100))";
    private static final String CREATE_USER_SQL = "create table if not exists survey_user(" +
            "id int primary key auto_increment," +
            "username varchar(50) unique," +
            "pwd varchar(50)," +
            "email varchar(256) unique," +
            "upload_file varchar(256)," +
            "disk_file varchar(256))";
    
    @Override
    public void contextInitialized(ServletContextEvent sce) {
        Connection connection = (Connection) sce.getServletContext().getAttribute("connection");
        try {
            Statement statement = connection.createStatement();
            statement.execute(CREATE_TABLE_SQL);
            statement.execute(CREATE_USER_SQL);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
